-- @owner: lihongji
-- @date: 2022-03-04
-- @testpoint: USTORE-connect by查询
--step1:建表;expect:成功
drop table if exists t_hash_group_by_011_1;
create table t_hash_group_by_011_1(
staff_id int not null,
course_name varchar(50),
higest_degree varchar(50) not null,
course_start_date date,
course_end_date date,
exam_date date,
score int) with (storage_type=ustore);
drop table if exists t_hash_group_by_011_2;
create table t_hash_group_by_011_2(
staff_id int not null,
higest_degree varchar(8) not null,
graduate_school varchar(64),
graduate_date date,
education_note varchar(70)) with (storage_type=ustore);
--step2:向表中插入数据;expect:成功
begin
	for i in 15..1000 loop
		insert into t_hash_group_by_011_1 values(1,'English','doctor','2017-06-15 12:00:00','2017-06-20 12:00:00','2017-06-25 12:00:00',90);
		insert into t_hash_group_by_011_1 values(2,'Painting','scholar','2018-06-15 12:00:00','2018-06-20 12:00:00','2018-06-25 12:00:00',null);
		insert into t_hash_group_by_011_1 values(3,'Drawing','doctor',null,'2018-06-27 12:00:00','2018-06-29 12:00:00',95);
		insert into t_hash_group_by_011_1 values(4,null,'master','2017-06-15 12:00:00','2017-06-20 12:00:00','2017-06-25 12:00:00',90);
		insert into t_hash_group_by_011_1 values(5,'System','master','2018-06-15 12:00:00','2018-06-20 12:00:00','2018-06-25 12:00:00',null);
		insert into t_hash_group_by_011_1 values(6,'Language','scholar','2018-06-25 12:00:00','2018-06-27 12:00:00','2018-06-29 12:00:00',95);
		insert into t_hash_group_by_011_1 values(7,'GRAMMER','doctor','2017-06-15 12:00:00',null,'2017-06-25 12:00:00',90);
		insert into t_hash_group_by_011_1 values(8,'Chinese','scholar','2018-06-15 12:00:00','2018-06-20 12:00:00','2018-06-25 12:00:00',null);
		insert into t_hash_group_by_011_1 values(9,'MATH','doctor','2018-06-25 12:00:00','2018-06-27 12:00:00','2018-06-29 12:00:00',95);
		insert into t_hash_group_by_011_1 values(10,'SQL majorization','doctor','2017-06-15 12:00:00','2017-06-20 12:00:00','2017-06-25 12:00:00',90);
		insert into t_hash_group_by_011_1 values(i-7,'BIG DATA','master','2018-06-15 12:00:00','2018-06-20 12:00:00','2018-06-25 12:00:00',null);
		insert into t_hash_group_by_011_1 values(i-6,'Performance Turning','scholar','2018-06-25 12:00:00','2018-06-27 12:00:00',null,95);
		insert into t_hash_group_by_011_1 values(i,'Performance Turning','scholar','2018-06-25 12:00:00','2018-06-27 12:00:00',null,95);
	end loop;

end;
/
begin
	for i in 15..20000 loop
		insert into t_hash_group_by_011_2 values(3,'doctor','Xidian University','2017-07-06 12:00:00','211');
		insert into t_hash_group_by_011_2 values(4,'doctor','Xidian University','2017-07-06 12:00:00','211');
		insert into t_hash_group_by_011_2 values(5,'master','Northwestern Polytechnical University','2017-07-06 12:00:00','211&985');
		insert into t_hash_group_by_011_2 values(6,'scholar','Peking University','2017-07-06 12:00:00','211&985');
		insert into t_hash_group_by_011_2 values(7,'scholar','Peking University','2017-07-06 12:00:00','211&985');
		insert into t_hash_group_by_011_2 values(8,'doctor','Xidian University','2017-07-06 12:00:00','211');
		insert into t_hash_group_by_011_2 values(i-8,'doctor','Xidian University','2017-07-06 12:00:00','211');
		insert into t_hash_group_by_011_2 values(11,'master','Northwestern Polytechnical University','2017-07-06 12:00:00','211&985');
		insert into t_hash_group_by_011_2 values(i+2,'scholar','Peking University','2017-07-06 12:00:00','211&985');
		insert into t_hash_group_by_011_2 values(i-10,'scholar','Peking University','2017-07-06 12:00:00','211&985');
		insert into t_hash_group_by_011_2 values(i,'scholar','Peking University','2017-07-06 12:00:00','211&985');
	end loop;

end;
/
--step3:connect by查询;expect:成功
explain plan for  select a.staff_id,b.higest_degree from t_hash_group_by_011_1 a inner join t_hash_group_by_011_2 b on a.staff_id=b.staff_id connect by prior a.staff_id=b.staff_id+2 group by a.staff_id,b.higest_degree;
--step4:清理环境;expect:成功
drop table if exists t_hash_group_by_011_1;
drop table if exists t_hash_group_by_011_2;
